﻿--系统表
create table [dbo].[AutoTableID](
	[TableName] [varchar](50) primary key,
	[MaxID] [int] not null
)
go
create procedure [dbo].[GetTablePKID]
	@TableName varchar(50),
	@MaxID int output
as
begin transaction
update [dbo].AutoTableID with   (updlock)   set MaxID = MaxID + 1 WHERE TableName = @TableName
select @MaxID=MaxID FROM [dbo].AutoTableID with   (xlock)  WHERE TableName = @TableName  
if @MaxID is null 
begin
	insert into [dbo].AutoTableID values (@TableName,1)
	select @MaxID = 1	
end 
if @@error = 0 
	commit transaction
else
begin
	rollback transaction
	select @MaxID = -1

end
GO
create table [dbo].Dept
(
	[ID]    int primary key,
	[Name]  varchar(100) not null,
	UpperID int not null,
	Depth   int not null,
	Path    varchar(100) not null,
	Orders  int
)
insert into [dbo].[dept] ([ID],[Name],[UpperID],[Depth],[Path],[Orders]) values  (1,'上海拜特信息技术有限公司',0,0,',',0);
go

create table [dbo].[User]
(
	[ID]      int primary key,	
	[Name]    varchar(50),
	LoginName varchar(50) not null,
	Password  varchar(50),
 	Orders    int ,
	Status    int default 0
)
--insert into [user] ([ID],[Name],[LoginName],[Password],[Orders],[Status]) values  (0,'超级管理员','admin','Tvx9JVTPj5E=',0,1);

go

create table [dbo].Module
(
	[ID]	int primary key,
	[Name]  varchar(100) not null,
	Code	varchar(20),
	UpperID int not null,
	Path	varchar(100) not null,
	Depth	int not null,
	Orders	int ,
	NavUrl varchar(200),
	IconUrl varchar(200),
	Remark	varchar(500),
	State int not null
)
go

create table [dbo].Role 
(
	[ID]			int primary key,
	[Name]			varchar(200) not null,
	TypeID			int  not null,
	Remark			varchar(500)
)
go
create table [dbo].DeptUser
(
	DeptID int foreign key references [dbo].Dept(ID) on delete cascade not null,
	UserID int foreign key references [dbo].[User](ID) on delete cascade not null,
	primary key (DeptID,UserID)
)

create table [dbo].RoleUser
(
	RoleID int ,
	UserID int foreign key references [dbo].[User]([ID]) on delete cascade,
	PRIMARY KEY (RoleID, UserID)
)
go
create table [dbo].ModuleRole
(
	ModuleID int foreign key references [dbo].Module([ID]) on delete cascade,
	RoleID   int,		
	primary key (ModuleID,RoleID)
)
go
create table [dbo].ModuleUser
(
	ModuleID int foreign key references [dbo].Module([ID]) on delete cascade,
	UserID   int foreign key references [dbo].[User](ID) on delete cascade,
	primary key (ModuleID,UserID)
)
go

create table [dbo].SystemRight
(
	RightID   int not null,
	OwnerID   int not null,
	OwnerType int not null,
	primary key(RightID,OwnerID,OwnerType)
)
create table [dbo].SystemRightSet
(
	RightIDs varchar(500),
)
go

create table [dbo].Dict
(
	ID		int primary key ,
	[Name]	varchar(200) not null,
	Code    int,
	UpperID int,
	Orders	int,
	Remark  varchar(500)
)
go

--日志表
create table [dbo].LogLogin
(
	ID			uniqueidentifier ROWGUIDCOL   primary key default newid(),
	UserID		int ,
	UserName	varchar(50) ,
	LoginName	varchar(50) not null,
	InputTime	datetime not null default getdate(),
	IP			varchar(20) not null,
	Result		int not null
)

create table [dbo].LogOperate
(
	ID			uniqueidentifier ROWGUIDCOL   primary key default newid(),
	UserID		int not null,
	UserName	varchar(50) not null,
	LoginName	varchar(50) not null,
	InputTime	datetime not null default getdate(),
	OperateType	int not null,
	TableName	varchar(100) not null,
	Remark		varchar(500) not null
)

create table LogMessage
(
	ID			uniqueidentifier ROWGUIDCOL   primary key default newid(),
	Name		nvarchar(100) ,
	[Message]	nvarchar(500),
	InputTime	datetime not null
)

create table LogException
(
	[ID]		 uniqueidentifier ROWGUIDCOL   primary key default newid(),
	[LoginName] [varchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[InputTime] [datetime] NOT NULL,
	[ErrorMessage] [nvarchar](500) NULL,
	[StackTrace] [nvarchar](2000) NULL,
	[Tag] [nvarchar](500) NULL,
)


--系统配置表
create table [dbo].SysConfig
(
	ID varchar(100) primary key,
	Config varchar(1000) not null,
)

CREATE TABLE [dbo].[Company](
	[ID] [int] primary key,
	[Name] [nvarchar](100) NOT NULL,
	[Address] [nvarchar](500) NULL,
	[ZipCode] [varchar](50) NULL,
	[BackName] [nvarchar](100) NULL,
	[BackAccount] [varchar](100) NULL,
	[Telephone] [varchar](50) NULL,
	[Mobile] [varchar](50) NULL,
	[Fax] [varchar](50) NULL,
	[Email] [varchar](100) NOT NULL,
	[QQ] [varchar](100) NULL,
	[MSN] [varchar](100) NULL,
	[ContactName] [nvarchar](50) NULL,
	[SiteUrl] [varchar](200) NULL,
	[SiteTitle] [nvarchar](100) NULL,
	[SiteKeywords] [nvarchar](1000) NULL,
	[SiteDescriptions] [nvarchar](1000) NULL,
	[SiteIPC] [varchar](100) NULL,
	[SiteIPCount] [int] NOT NULL,
	[SitePVCount] [int] NOT NULL,
 )

 --数据表
 create table Para
 (
	ID varchar(100) primary key,
	Value varchar(200) ,
	Remark varchar(500),
	Orders int not null,
 )

 --初始化数据
 
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (1,'系统模块',null,0,',',0,0,null,null,null);
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (2,'后台管理','',1,',1,',1,10,'','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (4,'用户管理','',31,',1,2,31,',3,0,'Admin/User/default.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (5,'部门管理',null,31,',1,2,31,',3,0,'Admin/Dept/List.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (6,'模块管理',null,32,',1,2,32,',3,0,'Admin/Module/List.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (7,'角色管理',null,33,',1,2,33,',3,0,'Admin/Role/List.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (28,'日志管理',null,2,',1,2,',2,4,'','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (29,'登录日志',null,28,',1,2,28,',3,0,'Admin/Log/LoginList.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (31,'组织架构',null,2,',1,2,',2,0,'','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (32,'所有模块',null,2,',1,2,',2,2,'','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (33,'用户角色',null,2,',1,2,',2,1,'','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (74,'数据字典','',2,',1,2,',2,3,'Common/Dict/Default.aspx','','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (77,'操作日志','',28,',1,2,28,',3,0,'Admin/Log/OperateList.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (78,'错误日志','',28,',1,2,28,',3,32,'admin/log/ErrorList.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (79,'信息日志','',28,',1,2,28,',3,43,'admin/log/messagelist.aspx','common/images/module/ico_031.gif','');
insert into [Module] ([ID],[Name],[Code],[UpperID],[Path],[Depth],[Orders],[NavUrl],[IconUrl],[Remark]) values  (80,'调度管理','',2,',1,2,',2,42,'admin/dispatch/list.aspx','','');

insert into autotableid (tablename,maxid) values('[Module]',100)

insert into [SysConfig] ([ID],[Config]) values  ('reginfo','AAEAAAD/////AQAAAAAAAAAMAgAAAD9KenkuQ29yZSwgVmVyc2lvbj0xLjAuMC4wLCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPW51bGwFAQAAACRKenkuQ29yZS5BZG1pbi5SZWdpc3Rlci5SZWdpc3RlckluZm8DAAAAGzxFeHBpcmVEYXRlPmtfX0JhY2tpbmdGaWVsZBQ8TWFjPmtfX0JhY2tpbmdGaWVsZBw8aGF2ZUV4cGlyZWQ+a19fQmFja2luZ0ZpZWxkAAEADQECAAAA/z839HUoyisGAwAAABEwMDoxQjozODozMzo3ODpBOQAL');


--USE [master]  

--EXEC dbo.sp_dbcmptlevel @dbname='数据库名', @new_cmptlevel=90  

--use [你的数据库名]  
--EXEC sp_changedbowner 'sa'   


--更改数据库用户映射
--exec sp_change_users_login 'Update_one',username,loginname